import xml.etree.cElementTree as ET
import MySQLdb
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

"""
site_file = "temp_sites"#"all_site_name"
table_type = "posts"
file_path = "D:\Research\ISEC-2014\data\content"
database = "isec2014"
user = "root"
password="123"
port=3306

"""
site_file = "all_site_name"
table_type = "posts"
file_path = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content"
database = "sangeeta"
user = "sangeetal"
password="sangeetal"
port=3307
#"""

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
insert_cursor = db1.cursor() 

file = open(site_file)
for line in file:
    site = line.strip() +".com"
    print  "site=",site
    table  = table_type+"_"+line.strip()+"_aug13"
    tree = ET.ElementTree(file=file_path+"\\"+site+"\\"+table_type+".xml")
    root= tree.getroot()
    #print root.tag, root.attrib
    count = 0
    for child_of_root in root:
        #print  child_of_root.attrib
        Body = ""
        Title = ""
        try:
            Body=(str)(child_of_root.attrib['Body'])
            Body = Body.replace("\n"," ")
            Body = Body.replace("'"," ")
            Body = Body.replace("\""," ")
            Body = Body.replace(",", " ")
            Body = Body.replace("(", " ")
            Body = Body.replace(")", " ")
            Body = Body.replace("\\", " ")
        except Exception ,e:
            #print "error"            
            count = count+1
            #print e, "count1=", count
             
        try:
            Title=(str)(child_of_root.attrib['Title'])
            Title = Title.replace("\n"," ")
            Title = Title.replace("'"," ")
            Title = Title.replace("\""," ")
            Title = Title.replace(",", " ")
            Title = Title.replace("(", " ")
            Title = Title.replace(")", " ")
            Title = Title.replace("\\", " ")
        except Exception ,e:
            #print "error"            
            count = count+1
            #print e, "count1=", count
        
        try:
            ParentId=(str)(child_of_root.attrib['ParentId'])
        except Exception, e:
            ParentId=-1
            count  =count+1 
            
        try:
            CommunityOwnedDate=(str)(child_of_root.attrib['CommunityOwnedDate'])
        except Exception, e:
            CommunityOwnedDate='0000-00-00 00:00:00'
            count  =count+1  
            
        try:
            CommentCount=(str)(child_of_root.attrib['CommentCount'])
        except Exception, e:
            CommentCount=0
            count  =count+1 
            
        try:
            OwnerUserId=(str)(child_of_root.attrib['OwnerUserId'])
        except Exception, e:
           OwnerUserId=-1
           count  =count+1   
           
        try:
            AcceptedAnswerId=(str)(child_of_root.attrib['AcceptedAnswerId'])
        except Exception, e:
           AcceptedAnswerId=-1
           count  =count+1     
        try:
            ViewCount=(str)(child_of_root.attrib['ViewCount'])
        except Exception, e:
           ViewCount=-1
           count  =count+1   
           
        try:
           LastEditorUserId=(str)(child_of_root.attrib['LastEditorUserId'])
        except Exception, e:
          LastEditorUserId=-1
          count  =count+1    
          
        try:
           LastEditDate=(str)(child_of_root.attrib['LastEditDate'])
        except Exception, e:
          LastEditDate='0000-00-00 00:00:00'
          count  =count+1
          
        try:
           Tags=(str)(child_of_root.attrib['Tags'])
        except Exception, e:
           Tags=''
           count  =count+1  
          
        try:
           AnswerCount=(str)(child_of_root.attrib['AnswerCount'])
        except Exception, e:
           AnswerCount=-1
           count  =count+1                   
                      
            
        """
        try:
            UserId_or_name=(str)(child_of_root.attrib['UserId'])
        except Exception, e:
            count  =count+1 
            try:
                UserId_or_name=(str)(child_of_root.attrib['UserDisplayName'])
                UserId_or_name = UserId_or_name.replace("'"," ")
            except Exception, e:
                UserId="not given"
                count = count+1"""
            
        insert_str = "insert into `"+ table+ "` values("+child_of_root.attrib['Id']+","+child_of_root.attrib['PostTypeId']+\
        ","+(str)(ParentId)+","+(str)(AcceptedAnswerId)+",'"+child_of_root.attrib['CreationDate']+"',"+\
        child_of_root.attrib['Score']+","+ (str)(ViewCount)+",'"+\
        Body+"',"+(str)(OwnerUserId)+","+\
        (str)(LastEditorUserId)+",'"+ (str)(LastEditDate)+"','"+\
        child_of_root.attrib['LastActivityDate']+"','"+ Title+"','"+Tags+\
        "',"+(str)(AnswerCount)+","+(str)(CommentCount)+\
        ",'"+ (str)(CommunityOwnedDate)+"')"
         
        #print "insert = ", insert_str
        insert_cursor.execute(insert_str)
        #break
    db1.commit()
    print "Number of errors = ", count



#http://css.dzone.com/articles/processing-xml-python